﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Media;
using System.Windows.Media.Imaging;

namespace MetroGS.AppClass
{
    class SQL
    {
        OleDbConnection conn = new OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0;data source=" + AppDomain.CurrentDomain.BaseDirectory + "\\DataContent\\gamesdb.mdb");
        string sqlresult1 = "";
        string sqlresult2 = "";
        string sqlresult3 = "";
        string sqlresult4 = "";
        string sqlresult5 = "";
        string sqlresult6 = "";
        string sqlresult7 = "";
        string sqlresult8 = "";
        string sqlresult9 = "";
        string sqlresult10 = "";
        string sqlresult11 = "";
        string sqlresult12 = "";
        string sqlresult13 = "";
        string sqlresult16 = "";
        string sqlresult17 = "";
        string sqlresult18 = "";

        string sqlresultt1 = "";
        string sqlresultt2 = "";
        string sqlresultt3 = "";
        string sqlresultt4 = "";
        string sqlresultt5 = "";

        public void gamesfill(Pages.UCGames Window, string sql)
        {
            Window.lv_gamelist.Items.Clear();
            OleDbCommand cmd = new OleDbCommand(sql, conn); conn.Open(); OleDbDataReader reader; reader = cmd.ExecuteReader();

            try
            {
                while (reader.Read())
                {
                    Window.lv_gamelist.Items.Add(reader.GetString(1).ToString());
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return;
            }
            finally
            {
                reader.Close(); conn.Close();
            }

            Window.lv_gamelist.SelectedIndex = 0;
        }

        public void datarefresh(Pages.UCGames Window)
        {
            if (Window.lv_gamelist.SelectedIndex != -1)
            {
                string gamename = Window.lv_gamelist.SelectedItem.ToString();
                string sql = "SELECT * FROM gamecentral WHERE gamename = '" + gamename + "'";

                Window.tb_title.Text = gamename;

                gamesresult(sql);

                Window.tb_type.Text = sqlresult6;

                DateTime m70 = DateTime.Now;
                string m700 = DateTime.Now.ToString("D");
                DateTime m71 = m70.AddDays(-1);
                string m711 = m71.ToString("D");
                DateTime m72 = m70.AddDays(-2);
                string m722 = m72.ToString("D");
                DateTime m73 = m70.AddDays(-3);
                string m733 = m73.ToString("D");
                DateTime m74 = m70.AddDays(-4);
                string m744 = m74.ToString("D");
                DateTime m75 = m70.AddDays(-5);
                string m755 = m75.ToString("D");
                DateTime m76 = m70.AddDays(-6);
                string m766 = m76.ToString("D");
                DateTime m77 = m70.AddDays(-7);
                string m777 = m77.ToString("D");

                if (sqlresult12 == DateTime.Now.ToString("D") || sqlresult12 == m711 || sqlresult12 == m722 || sqlresult12 == m733 || sqlresult12 == m744 || sqlresult12 == m755 || sqlresult12 == m766 || sqlresult12 == m777)
                {
                    Window.tb_status.Text = "In Progress";
                }
                else
                {
                    Window.tb_status.Text = "Unfinished";
                }

                if (sqlresult12 == "Never Played")
                {
                    Window.tb_status.Text = "Unstarted";
                }

                Window.tb_description.Text = sqlresult10;
                Window.tb_lastplayed.Text = sqlresult12;

                if (sqlresult2 == "NOT SET")
                {
                    Window.tb_backup.Text = "No";
                }
                else
                {
                    Window.tb_backup.Text = "Yes";
                }

                Window.tb_compatibility.Text = sqlresult3;

                gamestime(gamename);
                Window.tb_playtime.Text = sqlresultt1 + " / " + sqlresultt2;

                if (sqlresult13 == "Yes")
                {
                    Window.tb_status.Text = "Completed";

                    if (sqlresult16 != "Prior to WGM")
                    {
                        completiongstatus(gamename);
                        Window.tb_completed.Text = sqlresult16 + " Times";
                        Window.tb_completed2.Visibility = System.Windows.Visibility.Visible;
                        Window.tb_completed2.Text = sqlresultt3;
                    }
                    else
                    {
                        Window.tb_completed.Text = sqlresult16;
                        Window.tb_completed2.Visibility = System.Windows.Visibility.Hidden;
                    }
                }
                else
                {
                    Window.tb_completed2.Visibility = System.Windows.Visibility.Hidden;
                    Window.tb_completed.Text = "Never Completed";
                }

                if (sqlresult11 == "NOT SET")
                {
                    Window.tb_playtype.Text = "Unknow";
                }
                else
                {
                    Window.tb_playtype.Text = "HDD";
                }
                if (sqlresult11.Contains("steam"))
                {
                    Window.tb_playtype.Text = "Steam";
                }
                if (sqlresult11.Contains("Dolphin"))
                {
                    Window.tb_playtype.Text = "Wii";
                }

                if (File.Exists(AppDomain.CurrentDomain.BaseDirectory + @"\GamesCovers\" + gamename + ".jpg"))
                {
                    Window.img_cover.Stretch = Stretch.Fill;
                    Window.img_cover.Source = new BitmapImage(new Uri(AppDomain.CurrentDomain.BaseDirectory + @"\GamesCovers\" + gamename + ".jpg"));
                }
                else
                {
                    Window.img_cover.Stretch = Stretch.Uniform;
                    Window.img_cover.Source = new BitmapImage(new Uri(AppDomain.CurrentDomain.BaseDirectory + @"\Resources\NoCover.png"));
                }
            }
        }

        public void gamestime(string gamename)
        {
            DataTable dtResults = new DataTable();
            conn.Open();

            if (conn.State == ConnectionState.Open)
            {
                OleDbCommand dbCommand = conn.CreateCommand();
                dbCommand.CommandType = CommandType.Text;
                dbCommand.CommandText = "SELECT SUM([sessiontime]) AS TOTAL FROM timetrack WHERE gamelink = '" + gamename + "'";

                OleDbDataReader dbReader = dbCommand.ExecuteReader();

                if (dbReader.HasRows)
                    dtResults.Load(dbReader);

                dbReader.Close();
                conn.Close();
            }

            Int32 timeresult;
            Int32.TryParse(dtResults.Rows[0]["TOTAL"].ToString(), out timeresult);

            sqlresultt1 = timeresult + " Minutes";
            sqlresultt2 = timeresult / 60 + " Hours";
        }

        private void completiongstatus(string gamename)
        {
            DataTable dtResults = new DataTable();
            conn.Open();

            if (conn.State == ConnectionState.Open)
            {
                OleDbCommand dbCommand = conn.CreateCommand();
                dbCommand.CommandType = CommandType.Text;
                dbCommand.CommandText = "SELECT gamelink, completedstatus, completedtype, playday, playmonth, playyear FROM timetrack WHERE completedstatus Is Not Null AND gamelink = '" + gamename + "'";


                OleDbDataReader dbReader = dbCommand.ExecuteReader();

                if (dbReader.HasRows)
                    dtResults.Load(dbReader);

                dbReader.Close();
                conn.Close();
            }
            string t1 = dtResults.Rows[0]["completedtype"].ToString();
            string t2 = dtResults.Rows[0]["playyear"].ToString();
            string t3 = dtResults.Rows[0]["playmonth"].ToString();
            string t4 = dtResults.Rows[0]["playday"].ToString();

            switch (t3)
            {
                case "1":
                    t3 = "January";
                    break;
                case "2":
                    t3 = "February";
                    break;
                case "3":
                    t3 = "March";
                    break;
                case "4":
                    t3 = "April";
                    break;
                case "5":
                    t3 = "May";
                    break;
                case "6":
                    t3 = "June";
                    break;
                case "7":
                    t3 = "July";
                    break;
                case "8":
                    t3 = "August";
                    break;
                case "9":
                    t3 = "September";
                    break;
                case "10":
                    t3 = "October";
                    break;
                case "11":
                    t3 = "November";
                    break;
                case "12":
                    t3 = "December";
                    break;
            }

            sqlresultt3 = t3 + " " + t4 + ", " + t2 + " on " + t1;
        }

        public void gamesresult(string sql)
        {
            OleDbCommand cmd = new OleDbCommand(sql, conn); conn.Open(); OleDbDataReader reader; reader = cmd.ExecuteReader();

            try
            {
                while (reader.Read())
                {
                    //Game Name
                    sqlresult1 = reader.GetString(1).ToString();
                    //Save Game
                    sqlresult2 = reader.GetString(2).ToString();
                    //Compatibility
                    sqlresult3 = reader.GetString(3).ToString();
                    //Backup State
                    sqlresult4 = reader.GetString(4).ToString();
                    //Status
                    sqlresult5 = reader.GetString(5).ToString();
                    //Type
                    sqlresult6 = reader.GetString(6).ToString();
                    //Note
                    sqlresult7 = reader.GetString(7).ToString();
                    //Plus
                    sqlresult8 = reader.GetString(8).ToString();
                    //Moin
                    sqlresult9 = reader.GetString(9).ToString();
                    //Description
                    sqlresult10 = reader.GetString(10).ToString();
                    //Path
                    sqlresult11 = reader.GetString(11).ToString();
                    //Last Played
                    sqlresult12 = reader.GetString(12).ToString();
                    //Completed
                    sqlresult13 = reader.GetString(13).ToString();
                    //CompletedResult
                    sqlresult16 = reader.GetString(16).ToString();
                    //CompletedStyle
                    sqlresult17 = reader.GetString(17).ToString();
                    //Favorite
                    sqlresult18 = reader.GetString(18).ToString();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return;
            }
            finally
            {
                reader.Close(); conn.Close();
            }
        }

        public void gamessearch(Pages.UCSearch Window, string sql)
        {
            Window.lb_searchresult.Items.Clear();
            OleDbCommand cmd = new OleDbCommand(sql, conn); conn.Open(); OleDbDataReader reader; reader = cmd.ExecuteReader();
            int count = 0;

            try
            {
                while (reader.Read())
                {
                    Window.lb_searchresult.Items.Add(reader.GetString(1).ToString());
                    count++;
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return;
            }
            finally
            {
                reader.Close(); conn.Close();
                Window.tb_result.Text = count.ToString() + " result found";
            }
        }

        public void gamestart(string sqlcm)
        {
            gamesresult(sqlcm);

            try
            {
                var processStartInfo = new ProcessStartInfo(sqlresult11);
                processStartInfo.WorkingDirectory = System.IO.Path.GetDirectoryName(sqlresult11);
                Process gameProcess = Process.Start(processStartInfo);
            }
            catch (Exception ex)
            { Console.WriteLine(ex.Message); return; }

        }

        public void gamesupdate(string sql)
        {
            conn.Open();
            OleDbCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();
            conn.Close();
        }

        public void gamescomplecal(string gamename)
        {
            string sql = "SELECT * FROM gamecentral WHERE gamename = '" + gamename + "'";
            gamesresult(sql);

            if (sqlresult16 == "Prior to WGM" || sqlresult16 == "0")
            {
                sql = @"UPDATE gamecentral SET completed='Yes', completedresult='1' WHERE gamename = '" + gamename + "'";
                gamesupdate(sql);
            }

            if (sqlresult16 == "1")
            {
                int completednumber = Convert.ToInt32(sqlresult16);
                completednumber++;
                string completedsnumber = Convert.ToString(completednumber);

                sql = @"UPDATE gamecentral SET completed='Yes', completedresult='" + completedsnumber + "' WHERE gamename = '" + gamename + "'";
                gamesupdate(sql);
            }
        }

        public void total_gamestime(Pages.UCGeneralStats Window)
        {
            DataTable dtResults = new DataTable();
            conn.Open();

            int playday = DateTime.Today.Day;

            if (conn.State == ConnectionState.Open)
            {
                OleDbCommand dbCommand = conn.CreateCommand();
                dbCommand.CommandType = CommandType.Text;
                dbCommand.CommandText = "SELECT SUM([sessiontime]) AS TOTAL FROM timetrack";

                OleDbDataReader dbReader = dbCommand.ExecuteReader();

                if (dbReader.HasRows)
                    dtResults.Load(dbReader);

                dbReader.Close();
                conn.Close();
            }

            Int32 timeresult;
            Int32.TryParse(dtResults.Rows[0]["TOTAL"].ToString(), out timeresult);

            Window.tb_tgt.Text = timeresult + " Minutes / " + timeresult / 60 + " Hours";
        }

        public void avg_gamestime(Pages.UCGeneralStats Window)
        {
            DataTable dtResults = new DataTable();
            conn.Open();

            if (conn.State == ConnectionState.Open)
            {
                OleDbCommand dbCommand = conn.CreateCommand();
                dbCommand.CommandType = CommandType.Text;
                dbCommand.CommandText = "SELECT AVG([sessiontime]) AS AVERAGE FROM timetrack";

                OleDbDataReader dbReader = dbCommand.ExecuteReader();

                if (dbReader.HasRows)
                    dtResults.Load(dbReader);

                dbReader.Close();
                conn.Close();
            }
            string rawVal = dtResults.Rows[0]["AVERAGE"].ToString();
            Double numValM = Math.Round(Convert.ToDouble(rawVal), 1, MidpointRounding.ToEven);
            Double numValH = Math.Round(numValM / 60, 1, MidpointRounding.ToEven);
            string finValM = Convert.ToString(numValM);
            string finValH = Convert.ToString(numValH);

            Window.tb_agt.Text = numValM + " Minutes";
        }

        public void total_gamestimeday(Pages.UCGeneralStats Window)
        {
            DataTable dtResults = new DataTable();
            conn.Open();

            int playday = DateTime.Today.Day;

            if (conn.State == ConnectionState.Open)
            {
                OleDbCommand dbCommand = conn.CreateCommand();
                dbCommand.CommandType = CommandType.Text;
                dbCommand.CommandText = "SELECT SUM([sessiontime]) AS TOTAL FROM timetrack WHERE playday=" + playday + "";

                OleDbDataReader dbReader = dbCommand.ExecuteReader();

                if (dbReader.HasRows)
                    dtResults.Load(dbReader);

                dbReader.Close();
                conn.Close();
            }

            Int32 timeresult;
            Int32.TryParse(dtResults.Rows[0]["TOTAL"].ToString(), out timeresult);

            Window.tb_td.Text = timeresult + " Minutes / " + timeresult / 60 + " Hours";
        }

        public void total_gamestimeweek(Pages.UCGeneralStats Window)
        {
            DataTable dtResults = new DataTable();
            conn.Open();

            CultureInfo ciCurr = CultureInfo.CurrentCulture;
            int weekNum = ciCurr.Calendar.GetWeekOfYear(DateTime.Now, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);
            int playweek = weekNum;

            if (conn.State == ConnectionState.Open)
            {
                OleDbCommand dbCommand = conn.CreateCommand();
                dbCommand.CommandType = CommandType.Text;
                dbCommand.CommandText = "SELECT SUM([sessiontime]) AS TOTAL FROM timetrack WHERE playweek=" + playweek + "";

                OleDbDataReader dbReader = dbCommand.ExecuteReader();

                if (dbReader.HasRows)
                    dtResults.Load(dbReader);

                dbReader.Close();
                conn.Close();
            }

            Int32 timeresult;
            Int32.TryParse(dtResults.Rows[0]["TOTAL"].ToString(), out timeresult);

            Window.tb_tw.Text = timeresult + " Minutes / " + timeresult / 60 + " Hours";
        }

        public void total_gamestimemonth(Pages.UCGeneralStats Window)
        {
            DataTable dtResults = new DataTable();
            conn.Open();

            int playmonth = DateTime.Today.Month;

            if (conn.State == ConnectionState.Open)
            {
                OleDbCommand dbCommand = conn.CreateCommand();
                dbCommand.CommandType = CommandType.Text;
                dbCommand.CommandText = "SELECT SUM([sessiontime]) AS TOTAL FROM timetrack WHERE playmonth=" + playmonth + "";

                OleDbDataReader dbReader = dbCommand.ExecuteReader();

                if (dbReader.HasRows)
                    dtResults.Load(dbReader);

                dbReader.Close();
                conn.Close();
            }

            Int32 timeresult;
            Int32.TryParse(dtResults.Rows[0]["TOTAL"].ToString(), out timeresult);

            Window.tb_tm.Text = timeresult + " Minutes / " + timeresult / 60 + " Hours";
        }

        public void total_game(Pages.UCGeneralStats Window)
        {
            DataTable dtResults = new DataTable();
            conn.Open();

            if (conn.State == ConnectionState.Open)
            {
                OleDbCommand dbCommand = conn.CreateCommand();
                dbCommand.CommandType = CommandType.Text;
                dbCommand.CommandText = "SELECT Count(gamecentral.gamename) AS CountOfgamename FROM gamecentral";

                OleDbDataReader dbReader = dbCommand.ExecuteReader();

                if (dbReader.HasRows)
                    dtResults.Load(dbReader);

                dbReader.Close();
                conn.Close();
            }

            sqlresultt4 = dtResults.Rows[0]["CountOfgamename"].ToString();
            Window.tb_gil.Text = sqlresultt4 + " Games";
        }

        public void total_gamecompleted(Pages.UCGeneralStats Window)
        {
            DataTable dtResults = new DataTable();
            conn.Open();

            if (conn.State == ConnectionState.Open)
            {
                OleDbCommand dbCommand = conn.CreateCommand();
                dbCommand.CommandType = CommandType.Text;
                dbCommand.CommandText = "SELECT Count(gamecentral.gamename) AS CountOfgamename FROM gamecentral WHERE completed='Yes' OR gamecompatibility='Broken'";

                OleDbDataReader dbReader = dbCommand.ExecuteReader();

                if (dbReader.HasRows)
                    dtResults.Load(dbReader);

                dbReader.Close();
                conn.Close();
            }

            sqlresultt5 = dtResults.Rows[0]["CountOfgamename"].ToString();
            int CountOfgamenameCompleted = Convert.ToInt32(sqlresultt5);
            int CountOfgamename = Convert.ToInt32(sqlresultt4);

            decimal percent = (CountOfgamenameCompleted * 100) / CountOfgamename;

            Window.tb_cg.Text = sqlresultt5 + " Games / " + percent + "% of the Library";
        }

        public void topgame(Pages.topat Window)
        {
            DataTable dtResults = new DataTable();
            conn.Open();

            if (conn.State == ConnectionState.Open)
            {
                OleDbCommand dbCommand = conn.CreateCommand();
                dbCommand.CommandType = CommandType.Text;
                dbCommand.CommandText = "SELECT TOP 6 timetrack.gamelink AS GameName, Sum(timetrack.sessiontime) AS SumOfsessiontime FROM timetrack GROUP BY timetrack.gamelink ORDER BY Sum(timetrack.sessiontime) DESC;";

                OleDbDataReader dbReader = dbCommand.ExecuteReader();

                if (dbReader.HasRows)
                    dtResults.Load(dbReader);

                dbReader.Close();
                conn.Close();
            }

            string t1 = "0";
            //string t2 = "0";
            //string t3 = "0";
            //string t4 = "0";
            //string t5 = "0";
            try
            {
                Window.tb_gl1.Text = dtResults.Rows[0]["GameName"].ToString();
                Window.tb_tt1.Text = dtResults.Rows[0]["SumOfsessiontime"].ToString() + " Minutes";
                t1 = dtResults.Rows[0]["SumOfsessiontime"].ToString();
                //Window.label_top2.Content = dtResults.Rows[1]["GameName"].ToString();
                //Window.label_top22.Content = dtResults.Rows[1]["SumOfsessiontime"].ToString() + " Minutes";
                //t2 = dtResults.Rows[1]["SumOfsessiontime"].ToString();
                //Window.label_top3.Content = dtResults.Rows[2]["GameName"].ToString();
                //Window.label_top33.Content = dtResults.Rows[2]["SumOfsessiontime"].ToString() + " Minutes";
                //t3 = dtResults.Rows[2]["SumOfsessiontime"].ToString();
                //Window.label_top4.Content = dtResults.Rows[3]["GameName"].ToString();
                //Window.label_top44.Content = dtResults.Rows[3]["SumOfsessiontime"].ToString() + " Minutes";
                //t4 = dtResults.Rows[3]["SumOfsessiontime"].ToString();
                //Window.label_top5.Content = dtResults.Rows[4]["GameName"].ToString();
                //Window.label_top55.Content = dtResults.Rows[4]["SumOfsessiontime"].ToString() + " Minutes";
                //t5 = dtResults.Rows[4]["SumOfsessiontime"].ToString();
            }
            catch (Exception ex)
            { }

            int t1c = Convert.ToInt32(t1);
            //int t2c = Convert.ToInt32(t2);
            //int t3c = Convert.ToInt32(t3);
            //int t4c = Convert.ToInt32(t4);
            //int t5c = Convert.ToInt32(t5);

            if (File.Exists(AppDomain.CurrentDomain.BaseDirectory + @"\Gamecover\" + Window.tb_gl1.Text + ".jpg"))
            {
                Window.img_c1.Stretch = Stretch.Fill;
                Window.img_c1.Source = new BitmapImage(new Uri(AppDomain.CurrentDomain.BaseDirectory + @"\Gamecover\" + Window.tb_gl1.Text + ".jpg"));
            }
            //if (File.Exists(AppDomain.CurrentDomain.BaseDirectory + @"\Gamecover\" + Window.label_top2.Content + ".jpg"))
            //{
            //    Window.image_cover2.Stretch = Stretch.Fill;
            //    Window.image_cover2.Source = new BitmapImage(new Uri(AppDomain.CurrentDomain.BaseDirectory + @"\Gamecover\" + Window.label_top2.Content + ".jpg"));
            //}
            //if (File.Exists(AppDomain.CurrentDomain.BaseDirectory + @"\Gamecover\" + Window.label_top3.Content + ".jpg"))
            //{
            //    Window.image_cover3.Stretch = Stretch.Fill;
            //    Window.image_cover3.Source = new BitmapImage(new Uri(AppDomain.CurrentDomain.BaseDirectory + @"\Gamecover\" + Window.label_top3.Content + ".jpg"));
            //}
            //if (File.Exists(AppDomain.CurrentDomain.BaseDirectory + @"\Gamecover\" + Window.label_top4.Content + ".jpg"))
            //{
            //    Window.image_cover4.Stretch = Stretch.Fill;
            //    Window.image_cover4.Source = new BitmapImage(new Uri(AppDomain.CurrentDomain.BaseDirectory + @"\Gamecover\" + Window.label_top4.Content + ".jpg"));
            //}
            //if (File.Exists(AppDomain.CurrentDomain.BaseDirectory + @"\Gamecover\" + Window.label_top5.Content + ".jpg"))
            //{
            //    Window.image_cover5.Stretch = Stretch.Fill;
            //    Window.image_cover5.Source = new BitmapImage(new Uri(AppDomain.CurrentDomain.BaseDirectory + @"\Gamecover\" + Window.label_top5.Content + ".jpg"));
            //}
        }
    }
}
